{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 准备工作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "阿里研究院\n",
    "阿里健康\n",
    "阿里巴巴商学院\n",
    "阿里数据\n",
    "\n",
    "腾讯金融科技\n",
    "腾讯研究院\n",
    "腾讯媒体研究院\n",
    "腾讯云启研究院\n",
    "酷鹅用户研究院\n",
    "'''\n",
    "公众号 = \"蚂蚁金服科技\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "fn = { \"output\" : { \"公众号_htm_snippets\": \"data_raw_src/公众号_htm_snippets_{公众号}.tsv\",\n",
    "                    \"公众号_df\": \"data_raw_src/公众号_df_{公众号}.tsv\",\n",
    "                    \"公众号_xlsx\": \"data_sets/公众号_url_{公众号}.xlsx\" } \\\n",
    "      }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号（requests）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "5\n",
      "10\n",
      "15\n",
      "20\n"
     ]
    }
   ],
   "source": [
    "# 目标url\n",
    "\n",
    "import time\n",
    "import requests\n",
    "import pandas as pd\n",
    "import csv\n",
    "\n",
    "\n",
    "url = \"https://mp.weixin.qq.com/cgi-bin/appmsg\"\n",
    "\n",
    "# 使用Cookie，跳过登陆操作\n",
    "headers = {\n",
    "  \"Cookie\": \"pgv_pvi=8704569344; RK=UjQ9BLy2TA; ptcz=85e3dc6e9cb5cdf04e99d862b1e54a58c97a99efde63c9ce9bc296f32c7f5cac; ua_id=cwM6LMvbGUeynaG2AAAAANPVIrWSOcaMH41mcOCUgWo=; mm_lang=zh_CN; wxuin=89210501505377; pgv_pvid=8309706985; noticeLoginFlag=1; openid2ticket_oR4K-4hWvcV2BL-kahtFK8BEyd4k=cmk3qhrP47X3vvRj1WyY+kFXo2dxUDzpwgh+JSQuzww=; bizuin=3237931206; pgv_si=s9697594368; cert=al0SDNFzokZi9IoVpAlOKlt4CUnP6dxI; ticket_id=gh_a05e9811e510; ticket=142e6bd441e7c805197eba7f0cdfc4f649eddb61; uuid=483f4ef85ce38122db002abafd789050; data_bizuin=3237931206; data_ticket=5SCEKevUYg88JT2efS2y4liTXnlvMZc14Xu8tQgrHsNel9uWoI4UuGfOLWtXKyrM; slave_sid=VWpnTzhSYzB3SUNOMURBNVEyR3RxN0I3eUNVeGRtWWl2a0dzX3FjeUdPaHF0VVlpUDlhVVA5RUtmTmhlMndjc0NiR19PX0kxeXhsd1Z2S0xWQWdjYzV0MTdDdzBucVJhZ0V4dkh1NjdUV1JCTTAxbGVCS0hYYXo5cmJRN09SU3d5ZmVaRWJ2bFVra3QyQWto; slave_bizuin=3237931206; rand_info=CAESILCSTew2zO5lK9JK4ssNbHOcyY/+GUIU0P67gsuTcQpF; xid=49f1b29beacaf02ac5ecd03c24463744; openid2ticket_oxuj-wJEVDoQM1kAoL2hpnUx21Dw=SB7UgI1DwBYfEUPFNEwOdsT/tAOESpm9Z4xluhwfdDU=; slave_user=gh_a05e9811e510\",\n",
    "  \"User-Agent\": \"Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36\"}\n",
    "\n",
    "data = {\n",
    "    \"token\": \"167427102\",\n",
    "    \"lang\": \"zh_CN\",\n",
    "    \"f\": \"json\",\n",
    "    \"ajax\": \"1\",\n",
    "    \"action\": \"list_ex\",\n",
    "    \"begin\": \"0\",\n",
    "    \"count\": \"5\",\n",
    "    \"query\": \"\",\n",
    "    \"fakeid\": \"MzI0Nzc3MTQyMw==\",\n",
    "    \"type\": \"9\",\n",
    "}\n",
    "\n",
    "\n",
    "\n",
    "content_list=[]\n",
    "\n",
    "for i in range(5):\n",
    "    data[\"begin\"] = i*5\n",
    "    print(data[\"begin\"])\n",
    "    time.sleep(3)\n",
    "    # 使用get方法进行提交\n",
    "    content_json = requests.get(url, headers=headers, params=data).json()\n",
    "#     print(content_json)\n",
    "    # 返回了一个json，里面是每一页的数据\n",
    "    for item in content_json[\"app_msg_list\"]:\n",
    "    # 提取每页文章的标题及对应的url\n",
    "        items = []\n",
    "        items.append(item[\"title\"])\n",
    "        items.append(item[\"link\"])\n",
    "        items.append(item[\"create_time\"])\n",
    "        content_list.append(items)\n",
    "\n",
    "\n",
    "name=['title','link','create_time']\n",
    "test=pd.DataFrame(columns=name,data=content_list)\n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=\"蚂蚁金服科技_requests\")) as writer:\n",
    "    test.to_excel(writer)\n",
    "\n",
    "# test.to_csv(\"../微信公众号爬虫_zhichao/南方周末.csv\",mode='a',encoding='utf-8')\n",
    "# print(\"保存成功\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号（selenium）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from lxml.html import fromstring\n",
    "import time\n",
    "from random import random\n",
    "\n",
    "# when selenium main_content is used\n",
    "# Parses an HTML document from a string constant.  Returns the root nood\n",
    "# root = fromstring(df.loc[1,\"html_snippets\"]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 使用Selenium\n",
    "* 要更改 opts.binary_location 至自己本地的Chrome浏览器，建议portable\n",
    "* Chrome浏览器 和 chromedriver.exe要同版本号到小数后一位\n",
    "* 要确保可以 开启浏览器机器人\n",
    "* 要确保浏览器机器人 可以打开网页 driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator.USER-20190824NZ\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:18: DeprecationWarning: use options instead of chrome_options\n"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.desired_capabilities import DesiredCapabilities\n",
    "\n",
    "#caps=dict()\n",
    "#caps[\"pageLoadStrategy\"] = \"none\"   # Do not wait for full page load\n",
    "\n",
    "opts = webdriver.ChromeOptions()\n",
    "opts.add_argument('--no-sandbox')#解决DevToolsActivePort文件不存在的报错\n",
    "opts.add_argument('window-size=1920x3000') #指定浏览器分辨率\n",
    "opts.add_argument('--disable-gpu') #谷歌文档提到需要加上一这个属性来规避bug\n",
    "opts.add_argument('--hide-scrollbars') #隐藏滚动条, 应对些特殊页面\n",
    "#opts.add_argument('blink-settings=imagesEnabled=false') #不加载图片, 提升速度\n",
    "#opts.add_argument('--headless') #浏览器不提供可视化页面. linux下如果系统不支持可视化不加这条会启动失败\n",
    "\n",
    "opts.binary_location = r\"C:\\Program Files (x86)\\Google\\Chrome\\Application\\chrome.exe\" #\"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "\n",
    "# \"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "driver = webdriver.Chrome( chrome_options = opts) #desired_capabilities=caps,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填表登入"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "selenium 的定位方法\n",
    "* find_element_by_id &ensp;&ensp;&ensp;  根据标签id定位\n",
    "* find_element_by_name   &ensp;&ensp;&ensp; 根据标签的name定位\n",
    "* find_element_by_xpath  &ensp;&ensp;&ensp; 根据xpath定位\n",
    "* find_element_by_link_text  &ensp;&ensp;&ensp; 通过文字链接来定位元素\n",
    "* find_element_by_partial_link_text  &ensp;&ensp;&ensp;  通过文字链接来定位元素\n",
    "* find_element_by_tag_name  &ensp;&ensp;&ensp;  根据标签的名字定位\n",
    "* find_element_by_class_name  &ensp;&ensp;&ensp; 通过class name 定位\n",
    "* find_element_by_css_selector  &ensp;&ensp;&ensp;  根据元素属性来定位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "payload =  {\"account\": \"1634354797@qq.com\", \"password\": \"crain2361zoe***\"}\n",
    "# payload =  {\"account\": \"NFUHacks@163.com\", \"password\": \"NFU706947580\"}\n",
    "driver.find_element_by_xpath('//div[@class=\"login__type__container login__type__container__scan\"]/a').click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "WebDriver 常用方法：\n",
    "* clear()清楚文本\n",
    "* send_keys(values)模拟按键输入\n",
    "* click()模拟点击\n",
    "* submit模拟提交"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').send_keys(payload['account'])\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').send_keys(payload['password'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//div[@class=\"login_btn_panel\"]/a').click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 点选单"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "其他常用方法\n",
    "* size：返回元素的尺寸\n",
    "* text：获取元素的文本\n",
    "* get_attribute：获取属性值  &ensp;&ensp;&ensp; get_attribute('innerHTML')获取元素内的全部HTML\n",
    "* is_displayed()：设置该元素用户是否可见"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'展开'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//a[@id=\"m_open\"]')\n",
    "element.click()\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.execute_script(\"window.scrollTo(0,document.body.scrollHeight)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://mp.weixin.qq.com/cgi-bin/appmsg?begin=0&count=10&t=media/appmsg_list&type=10&action=list&token=1438818190&lang=zh_CN'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//li[@title[contains(.,\"素材管理\")]]/a') \n",
    "# main_content = element.get_attribute('innerHTML')\n",
    "# main_content\n",
    "url2= element.get_attribute(\"href\")\n",
    "url2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(url2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新建图文消息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"新建图文消息\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['CDwindow-46E76D0ECAD9235C70BE3218AC5AB3A6', 'CDwindow-ACF2D986615D4047CEBB3513E1C62A10']\n"
     ]
    }
   ],
   "source": [
    "print (driver.window_handles)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新建图文消息开了另一分视窗，所以要切换 switch_to \n",
    "driver.switch_to.window(driver.window_handles[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 超链接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                超链接              \n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"超链接\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "选择其他公众号\n"
     ]
    }
   ],
   "source": [
    "# 点 选择其他公众号\n",
    "element = driver.find_element_by_xpath('//*[text()[contains(.,\"选择其他公众号\")]]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').clear()\n",
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').send_keys(公众号)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-icon weui-desktop-icon__inputSearch weui-desktop-icon__small\"><!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <svg width=\"16\" height=\"16\" viewBox=\"0 0 16 16\" xmlns=\"http://www.w3.org/2000/svg\"><path d=\"M11.33 10.007l4.273 4.273a.502.502 0 0 1 .005.709l-.585.584a.499.499 0 0 1-.709-.004L10.046 11.3a6.278 6.278 0 1 1 1.284-1.294zm.012-3.729a5.063 5.063 0 1 0-10.127 0 5.063 5.063 0 0 0 10.127 0z\"></path></svg> <!----> <!----> <!----> <!----></div>\n"
     ]
    }
   ],
   "source": [
    "# 点放大镜搜\n",
    "element = driver.find_element_by_xpath('//button[@class=\"weui-desktop-icon-btn weui-desktop-search__btn\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ibic8Ff0hib2zM1PNggypmobkam18vP1Nh7kaBU77zrJ8WD9vd8nj4uicvXIFXu1Ek0ScZGia91Clw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服科技</strong> <i class=\"inner_link_account_wechat\">微信号：Ant-Techfin</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicVQBHK655aKVDMOs2fbSdQb0Zxic4ibIGJUNAayAjy3yCeWqSFe4GVNhExtpNt4opiavkicHGHI1hQ/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服</strong> <i class=\"inner_link_account_wechat\">微信号：mayi-jinfu</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9KyndRqTmA3nJJpuL3eCmHqF9L5FzIQ3aoG8fH3gZT8dT3uZyD9YYvA9dlwTsHDfEtgW9hVt5jKHkg/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">山东蚂蚁金服网络科技有限公司</strong> <i class=\"inner_link_account_wechat\">微信号：SDMYJF_FWH</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuySfict4E8oUlkK2307gQnOiaa93h83mib5Wcp3oKp570jgcWdSBiatwvU9nT2Hgs9Qzg0tvJUU8sw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服花呗</strong> <i class=\"inner_link_account_wechat\">微信号：mayi_huabei</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/njNOiayhTg0vmmugt6hiaJVic45dibuWblwcsPibP9D2E8iaHbDWSW86DnhXS2faddpqicd9dKL3t0AZF6TXlzCdEys2A/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服安全响应中心</strong> <i class=\"inner_link_account_wechat\">微信号：未设置</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "公众号SERP = main_content\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 解析\n",
    "root = fromstring(公众号SERP) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "主 = root.xpath('//li[@class=\"inner_link_account_item\"]')\n",
    "\n",
    "account_list = []\n",
    "for e in 主:\n",
    "    account_nickname = e.xpath('./div/strong[@class=\"inner_link_account_nickname\"]')[0].text\n",
    "    account_wechat = e.xpath('./div/i[@class=\"inner_link_account_wechat\"]')[0].text\n",
    "    account_img = e.xpath('./div/img/@src')[0]\n",
    "    account = {\"nickname\": account_nickname, \"wechat\": account_wechat, \"img\": account_img,}\n",
    "    account_list.append(account)\n",
    "\n",
    "df_account = pd.DataFrame(account_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nickname</th>\n",
       "      <th>wechat</th>\n",
       "      <th>img</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>蚂蚁金服科技</td>\n",
       "      <td>微信号：Ant-Techfin</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>蚂蚁金服</td>\n",
       "      <td>微信号：mayi-jinfu</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>山东蚂蚁金服网络科技有限公司</td>\n",
       "      <td>微信号：SDMYJF_FWH</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9Kyn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>蚂蚁金服花呗</td>\n",
       "      <td>微信号：mayi_huabei</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuy...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>蚂蚁金服安全响应中心</td>\n",
       "      <td>微信号：未设置</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/njNOiayhTg0vmmu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         nickname           wechat  \\\n",
       "0          蚂蚁金服科技  微信号：Ant-Techfin   \n",
       "1            蚂蚁金服   微信号：mayi-jinfu   \n",
       "2  山东蚂蚁金服网络科技有限公司   微信号：SDMYJF_FWH   \n",
       "3          蚂蚁金服花呗  微信号：mayi_huabei   \n",
       "4      蚂蚁金服安全响应中心          微信号：未设置   \n",
       "\n",
       "                                                 img  \n",
       "0  http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ib...  \n",
       "1  http://mmbiz.qpic.cn/mmbiz_png/rS76otFH2pNlicV...  \n",
       "2  http://mmbiz.qpic.cn/mmbiz_png/ibouibdiaMA9Kyn...  \n",
       "3  http://mmbiz.qpic.cn/mmbiz_png/cia3tMb8xpGJSuy...  \n",
       "4  http://mmbiz.qpic.cn/mmbiz_png/njNOiayhTg0vmmu...  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/8zGb2W5DJWvF1ibic8Ff0hib2zM1PNggypmobkam18vP1Nh7kaBU77zrJ8WD9vd8nj4uicvXIFXu1Ek0ScZGia91Clw/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">蚂蚁金服科技</strong> <i class=\"inner_link_account_wechat\">微信号：Ant-Techfin</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]/li')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n跳转_input = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/input\\')\\n跳转_a = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/a\\')\\n跳转_input.clear()\\n跳转_input.send_keys(2)\\n跳转_a.click()\\n'"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 跳转testing\n",
    "'''\n",
    "跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "跳转_input.clear()\n",
    "跳转_input.send_keys(2)\n",
    "跳转_a.click()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 99]\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "# 跳转上限\n",
    "l_e = driver.find_elements_by_xpath('//label[@class=\"weui-desktop-pagination__num\"]')\n",
    "l_e_int  = [int(x.text) for x in l_e] \n",
    "print (l_e_int)\n",
    "print (l_e_int[0]==l_e_int[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99]\n"
     ]
    }
   ],
   "source": [
    "pages = list(range(l_e_int[0],l_e_int[-1]+1 ))\n",
    "#print(pages[0:2])\n",
    "pages = list(range(1,l_e_int[-1]+1 ))\n",
    "print(pages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 循环/遍历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# global varialbes \n",
    "html_raw = dict()\n",
    "main_content =\"\"\n",
    "element = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_pages (pages):\n",
    "    for p in pages:\n",
    "        print (p,end='\\t')\n",
    "\n",
    "        跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "        跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "        跳转_input.clear()\n",
    "        跳转_input.send_keys(p)\n",
    "        跳转_a.click()\n",
    "\n",
    "        time.sleep(45+120*random())\n",
    "\n",
    "        element = driver.find_element_by_xpath('//div[@class=\"inner_link_article_list\"]')\n",
    "        main_content = element.get_attribute('innerHTML')\n",
    "        #print(main_content)\n",
    "        html_raw[p] = main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\t2\t3\t4\t5\t6\t7\t8\t9\t10\t11\t12\t13\t14\t15\t16\t17\t18\t19\t20\t21\t22\t23\t24\t25\t26\t27\t28\t29\t30\t31\t32\t33\t34\t35\t36\t37\t38\t39\t40\t41\t42\t43\t44\t45\t46\t47\t48\t49\t50\t51\t52\t53\t54\t55\t56\t57\t58\t59\t60\t61\t62\t63\t64\t65\t66\t67\t68\t69\t70\t71\t72\t73\t74\t75\t76\t77\t78\t79\t80\t81\t82\t83\t84\t85\t86\t87\t88\t89\t90\t91\t92\t93\t94\t95\t96\t97\t98\t99\t"
     ]
    }
   ],
   "source": [
    "process_pages(pages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        html_snippets\n",
       "1   <div><label class=\"inner_link_article_item\"><s...\n",
       "2   <div><label class=\"inner_link_article_item\"><s...\n",
       "3   <div><label class=\"inner_link_article_item\"><s...\n",
       "4   <div><label class=\"inner_link_article_item\"><s...\n",
       "5   <div><label class=\"inner_link_article_item\"><s...\n",
       "..                                                ...\n",
       "95  <div><label class=\"inner_link_article_item\"><s...\n",
       "96  <div><label class=\"inner_link_article_item\"><s...\n",
       "97  <div><label class=\"inner_link_article_item\"><s...\n",
       "98  <div><label class=\"inner_link_article_item\"><s...\n",
       "99  <div><label class=\"inner_link_article_item\"><s...\n",
       "\n",
       "[99 rows x 1 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([html_raw]).T\n",
    "df.columns = [\"html_snippets\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'html_raw' (dict)\n"
     ]
    }
   ],
   "source": [
    "%store html_raw\n",
    "import pickle \n",
    "filehandler = open(\"html_raw\", 'wb') \n",
    "pickle.dump(html_raw, filehandler)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "99\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [html_snippets]\n",
       "Index: []"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out = df[~df.duplicated()]\n",
    "print (len(df_out))\n",
    "df[df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try_again = list(df[df.duplicated()].index)\n",
    "print(try_again)\n",
    "try_again = try_again + list (set(pages).difference(set(df.index.values)))\n",
    "try_again"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 暂存档"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = fn [\"output\"] [\"公众号_htm_snippets\"] \n",
    "df_out.to_csv(filename.format(公众号=公众号), sep=\"\\t\", encoding=\"utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,7,5,5,5,5,5,8,5,6,5,5,5,5,5,5,5,5,5,5,5,5,5,5,6,5,5,5,5,5,5,5,5,5,5,6,9,6,6,5,5,6,7,6,5,5,7,9,15,9,7,7,9,8,6,5,5,5,5,5,7,5,8,6,5,6,6,5,5,5,5,5,5,5,6,5,5,5,5,5,7,4,"
     ]
    }
   ],
   "source": [
    "def parse_html_snippets(_snippet_):\n",
    "    root = fromstring(_snippet_) \n",
    "    title = [x.text for x in root.xpath('//div[@class=\"inner_link_article_title\"]')]\n",
    "    create_time = [x.text for x in root.xpath('//div[@class=\"inner_link_article_date\"]')]\n",
    "    link = [x for x in root.xpath('//a/@href')]\n",
    "    _df_ = pd.DataFrame({\"title\":title, \"create_time\": create_time, \"link\":link})\n",
    "    return(_df_)\n",
    "    \n",
    "l_df = []\n",
    "for p in pages:\n",
    "    _df_ = parse_html_snippets(df.loc[p,\"html_snippets\"])\n",
    "    print (len(_df_), end=\",\")\n",
    "    l_df.append(_df_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>重磅首发！免费下载！《Java 开发手册》泰山版王者归来！</td>\n",
       "      <td>2020-04-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020支付宝春招大盘点：技术人，你五行缺蚂蚁</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>蚂蚁前端的开局十年：一切都在路上</td>\n",
       "      <td>2020-04-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>“三清”博士，为何选择支付宝机密计算？</td>\n",
       "      <td>2020-04-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>从上千篇论文脱颖而出，支付宝论文凭什么获得WWW2020评委认可？</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>校招季丨支付宝有一个疯起来连自己都要打的团队，你要加入吗</td>\n",
       "      <td>2020-04-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title create_time  \\\n",
       "0        蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”  2020-05-13   \n",
       "1                美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "2   支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "3      【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "4          含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？  2020-04-24   \n",
       "5            重磅首发！免费下载！《Java 开发手册》泰山版王者归来！  2020-04-22   \n",
       "6                  2020支付宝春招大盘点：技术人，你五行缺蚂蚁  2020-04-19   \n",
       "7                         蚂蚁前端的开局十年：一切都在路上  2020-04-18   \n",
       "8                      “三清”博士，为何选择支付宝机密计算？  2020-04-17   \n",
       "9        从上千篇论文脱颖而出，支付宝论文凭什么获得WWW2020评委认可？  2020-04-15   \n",
       "10            校招季丨支付宝有一个疯起来连自己都要打的团队，你要加入吗  2020-04-14   \n",
       "\n",
       "                                                 link  \n",
       "0   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "1   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "2   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "5   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "6   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "7   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "8   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "9   http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "10  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out = pd.concat(l_df).reset_index(drop=True)\n",
    "df_url_out.loc[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>550</th>\n",
       "      <td>蚂蚁金服首席战略官陈龙：数字普惠金融 正在发生的未来</td>\n",
       "      <td>2017-03-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>区块链技术落地为何这么难？蚂蚁金服说有这3大挑战</td>\n",
       "      <td>2017-03-02</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>蚂蚁金服与UC Berkeley RISE实验室启动合作，加速数据人才培养</td>\n",
       "      <td>2017-03-02</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>553</th>\n",
       "      <td>蚂蚁金服CTO程立：数字普惠金融2.0时代要解决的九大难题</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title create_time  \\\n",
       "550             蚂蚁金服首席战略官陈龙：数字普惠金融 正在发生的未来  2017-03-06   \n",
       "551               区块链技术落地为何这么难？蚂蚁金服说有这3大挑战  2017-03-02   \n",
       "552  蚂蚁金服与UC Berkeley RISE实验室启动合作，加速数据人才培养  2017-03-02   \n",
       "553          蚂蚁金服CTO程立：数字普惠金融2.0时代要解决的九大难题  2017-02-28   \n",
       "554            一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录  2017-02-28   \n",
       "\n",
       "                                                  link  \n",
       "550  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "551  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "552  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "553  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "554  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>value</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>重磅首发！免费下载！《Java 开发手册》泰山版王者归来！</td>\n",
       "      <td>2020-04-22</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020支付宝春招大盘点：技术人，你五行缺蚂蚁</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>蚂蚁前端的开局十年：一切都在路上</td>\n",
       "      <td>2020-04-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>533</th>\n",
       "      <td>教程|如何快速开发支付宝小程序？</td>\n",
       "      <td>2017-09-14</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>活动|百万奖金等你来战——蚂蚁开发者大赛盛大开启</td>\n",
       "      <td>2017-08-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>深度学习图像技术的又一突破性应用：蚂蚁金服发布“定损宝”</td>\n",
       "      <td>2017-06-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>549</th>\n",
       "      <td>支付宝“刷脸”用户已超过1.5亿人 |  独家专访蚂蚁金服生物识别技术负责人陈继东</td>\n",
       "      <td>2017-03-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>327 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title create_time  \\\n",
       "value                                                          \n",
       "1                      美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "3            【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "5                  重磅首发！免费下载！《Java 开发手册》泰山版王者归来！  2020-04-22   \n",
       "6                        2020支付宝春招大盘点：技术人，你五行缺蚂蚁  2020-04-19   \n",
       "7                               蚂蚁前端的开局十年：一切都在路上  2020-04-18   \n",
       "...                                          ...         ...   \n",
       "533                             教程|如何快速开发支付宝小程序？  2017-09-14   \n",
       "535                     活动|百万奖金等你来战——蚂蚁开发者大赛盛大开启  2017-08-18   \n",
       "543                 深度学习图像技术的又一突破性应用：蚂蚁金服发布“定损宝”  2017-06-28   \n",
       "549    支付宝“刷脸”用户已超过1.5亿人 |  独家专访蚂蚁金服生物识别技术负责人陈继东  2017-03-06   \n",
       "554                  一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录  2017-02-28   \n",
       "\n",
       "                                                    link  \n",
       "value                                                     \n",
       "1      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "5      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "6      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "7      http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "...                                                  ...  \n",
       "533    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "535    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "543    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "549    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "554    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[327 rows x 3 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tagging 标记\n",
    "tagging_list = [\"\",\"马化腾\", \"腾讯\", \"微众银行\",\"腾讯复星\",\"腾讯风控\",\"腾讯支付\",\"WeChat\",\"We Remit\",\\\n",
    "                \"公益日\",\"红包\",\\\n",
    "                \"腾讯财付通\",\"鹅厂\",\"QQ钱包\",\"QQ红包\",\"QQ\",\\\n",
    "                \"只有一\",\"大咖\",\"听说\",\"图片\",\"照片\",\"小编\",\\\n",
    "                \"洗钱\", \"黑产\",\"被骗\",\"腾讯安全课\",\"诈骗\", \"炒股\",\"神秘兼职\",\"神秘组织\",\\\n",
    "                \"财付通\", \"品牌\",\\\n",
    "                \"收款\",\\\n",
    "                \"报告\",\\\n",
    "                \"银行卡\",\"理财\",\"选股\",\"发票\",\"基金\",\\\n",
    "                \"区块链\",\"金融云\",\"O2O\",\"农产品\",\"家乡\",\\\n",
    "                \"数据\", \"数据赋能\", \"智能\", \"数字孪生\", \"智慧大脑\",\\\n",
    "                \"出行\",\"乘车\",\"公交\",\"乘车码\", \"智慧地铁\",\\\n",
    "                \"高峰论坛\", \"智库\",\\\n",
    "                \"央行\",\"新规\", \\\n",
    "                \"微信\", \"微信支付\", \"跨境支付\", \"移动支付\",\"非银行支付\",\"电子支付\",\\\n",
    "                \"互联网金融\", \"金融科技\",\"互联网＋\",\"互联网+金融\",\"普惠金融\",\"虚拟银行\",\\\n",
    "                \"开放\",\"生态\",\"复杂\",\"互联网思维\",\"全球合作伙伴\",\\\n",
    "                \"联合国\", \"城市\", \"粤港澳大湾区\", \"平台\", \"可持续发展\", \"未来\", \"绿色\",\\\n",
    "                \"医护\",\"防护服\",\"小时\",\"武汉\",\"危机\",\"新冠肺炎\", \"疫\", \"疫情\", \"复工\",\"停课\",\"宅经济\",\\\n",
    "                \"基建\",\"新基建\"] #overwritable\n",
    "\n",
    "v_v_list = []\n",
    "\n",
    "for tag in tagging_list:\n",
    "    index_list = df_url_out [ df_url_out.title.str.contains(tag) ].index.tolist()\n",
    "    v_v_pairs = pd.DataFrame({tag:index_list}).melt().set_index(\"value\")\n",
    "    v_v_list.append(v_v_pairs)\n",
    "\n",
    "df_cat = v_v_list[0]\n",
    "for d in v_v_list:\n",
    "    df_cat.update(d)\n",
    "    \n",
    "# 尚未标记内容\n",
    "df_url_out.loc [ df_cat.query('variable==\"\"').index ]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "df_url_out.loc[53].link"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [title, create_time, link]\n",
       "Index: []"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out[df_url_out.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>550</th>\n",
       "      <td>蚂蚁金服首席战略官陈龙：数字普惠金融 正在发生的未来</td>\n",
       "      <td>2017-03-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>区块链技术落地为何这么难？蚂蚁金服说有这3大挑战</td>\n",
       "      <td>2017-03-02</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>蚂蚁金服与UC Berkeley RISE实验室启动合作，加速数据人才培养</td>\n",
       "      <td>2017-03-02</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>553</th>\n",
       "      <td>蚂蚁金服CTO程立：数字普惠金融2.0时代要解决的九大难题</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>555 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      title create_time  \\\n",
       "0         蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”  2020-05-13   \n",
       "1                 美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "2    支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "3       【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "4           含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？  2020-04-24   \n",
       "..                                      ...         ...   \n",
       "550              蚂蚁金服首席战略官陈龙：数字普惠金融 正在发生的未来  2017-03-06   \n",
       "551                区块链技术落地为何这么难？蚂蚁金服说有这3大挑战  2017-03-02   \n",
       "552   蚂蚁金服与UC Berkeley RISE实验室启动合作，加速数据人才培养  2017-03-02   \n",
       "553           蚂蚁金服CTO程立：数字普惠金融2.0时代要解决的九大难题  2017-02-28   \n",
       "554             一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录  2017-02-28   \n",
       "\n",
       "                                                  link  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "..                                                 ...  \n",
       "550  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "551  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "552  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "553  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "554  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...  \n",
       "\n",
       "[555 rows x 3 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out[~df_url_out.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>智能</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>美女妈妈的十年阿里路，从一线研发到技术leader</td>\n",
       "      <td>2020-05-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路</td>\n",
       "      <td>2020-04-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>数据</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>智能</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>550</th>\n",
       "      <td>蚂蚁金服首席战略官陈龙：数字普惠金融 正在发生的未来</td>\n",
       "      <td>2017-03-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>未来</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>区块链技术落地为何这么难？蚂蚁金服说有这3大挑战</td>\n",
       "      <td>2017-03-02</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>区块链</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>蚂蚁金服与UC Berkeley RISE实验室启动合作，加速数据人才培养</td>\n",
       "      <td>2017-03-02</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>数据</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>553</th>\n",
       "      <td>蚂蚁金服CTO程立：数字普惠金融2.0时代要解决的九大难题</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>普惠金融</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>555 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      title create_time  \\\n",
       "0         蚂蚁共享智能实验室首席科学家张本宇：如何用共享智能架设数据“高铁”  2020-05-13   \n",
       "1                 美女妈妈的十年阿里路，从一线研发到技术leader  2020-05-09   \n",
       "2    支付宝研究员兼OceanBase总架构师杨传辉：我在数据库梦之队的十年成长路  2020-04-27   \n",
       "3       【送书】900页阿里文娱技术精选、覆盖8大技术线， 一览智慧文娱精华！  2020-04-26   \n",
       "4           含ppt下载 | 蚂蚁金服凭什么发布全国首个共享智能联盟标准？  2020-04-24   \n",
       "..                                      ...         ...   \n",
       "550              蚂蚁金服首席战略官陈龙：数字普惠金融 正在发生的未来  2017-03-06   \n",
       "551                区块链技术落地为何这么难？蚂蚁金服说有这3大挑战  2017-03-02   \n",
       "552   蚂蚁金服与UC Berkeley RISE实验室启动合作，加速数据人才培养  2017-03-02   \n",
       "553           蚂蚁金服CTO程立：数字普惠金融2.0时代要解决的九大难题  2017-02-28   \n",
       "554             一秒记账12万笔，「超级会计师」是怎样创造这个世界纪录  2017-02-28   \n",
       "\n",
       "                                                  link variable  \n",
       "0    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       智能  \n",
       "1    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "2    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       数据  \n",
       "3    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "4    http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       智能  \n",
       "..                                                 ...      ...  \n",
       "550  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       未来  \n",
       "551  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...      区块链  \n",
       "552  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...       数据  \n",
       "553  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     普惠金融  \n",
       "554  http://mp.weixin.qq.com/s?__biz=MzI0Nzc3MTQyMw...     无法分类  \n",
       "\n",
       "[555 rows x 4 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o = df_url_out.join(df_cat).replace(\"\", np.nan).fillna(\"无法分类\")\n",
    "df_o"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [title, create_time, link, variable]\n",
       "Index: []"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"腾讯\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>无法分类</th>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>数据</th>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>开放</th>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>智能</th>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>区块链</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>平台</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融科技</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>未来</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>生态</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城市</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>小时</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融云</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>疫情</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>普惠金融</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>红包</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>武汉</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>疫</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>基金</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>报告</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>医护</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>理财</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>智库</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>出行</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>听说</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>移动支付</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>图片</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>全球合作伙伴</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          title\n",
       "variable       \n",
       "无法分类        327\n",
       "数据           49\n",
       "开放           30\n",
       "智能           29\n",
       "区块链          21\n",
       "平台           18\n",
       "金融科技         16\n",
       "未来           13\n",
       "生态           10\n",
       "城市            6\n",
       "小时            4\n",
       "金融云           4\n",
       "疫情            4\n",
       "普惠金融          4\n",
       "红包            3\n",
       "武汉            3\n",
       "疫             2\n",
       "基金            2\n",
       "报告            2\n",
       "医护            1\n",
       "理财            1\n",
       "智库            1\n",
       "出行            1\n",
       "听说            1\n",
       "移动支付          1\n",
       "图片            1\n",
       "全球合作伙伴        1"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stats = df_o.groupby(by=\"variable\").agg({\"title\":\"count\"}).sort_values(by=\"title\", ascending=False)\n",
    "df_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_account.columns.name = \"rel_accounts\"\n",
    "df_o.columns.name = \"url_cat\"\n",
    "df_stats.columns.name = \"stats\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "_df_.columns.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the xlsxwriter workbook and worksheet objects.  \n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=公众号)) as writer:\n",
    "    workbook  = writer.book\n",
    "\n",
    "    for _df_ in [df_account, df_o, df_stats]:\n",
    "        _df_.to_excel(writer, sheet_name = _df_.columns.name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_url_out.to_excel('公众号_url_蚂蚁金服科技_requests.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
